<?php
/*
# ------------------------------------------------------------------------
# JA Job Board Package
# ------------------------------------------------------------------------
# Copyright (C) 2004-2010 JoomlArt.com. All Rights Reserved.
# @license GNU/GPLv3 http://www.gnu.org/licenses/gpl-3.0.html
# Author: JoomlArt.com
# Websites: http://www.joomlart.com - http://www.joomlancers.com.
# ------------------------------------------------------------------------
*/

defined('_JEXEC') or die();

jimport( 'joomla.application.component.model' );

require_once(JPATH_COMPONENT.DS.'asset'.DS.'XMLExportHelper.php');

class JAJobBoardModelJAXMLExport extends JModel
{
	var $_id;
	var $_db;
	var $_data;
    var $_table;
    var $_pagination;
    
    /**
    * Return the query is used to retrive all row from database
    * @return string The query is used 
    */
    function getItems($SchemaID){
    	global $mainframe;    	    	
    	
    	$db = & JFactory::getDBO();
    	
    	$limit		= $mainframe->getUserStateFromRequest( 'global.list.limit',		'limit',		$mainframe->getCfg('list_limit'), 'int' );
		$limitstart	= $mainframe->getUserStateFromRequest( JBCOMNAME.'.jaxmlexport.limitstart',	'limitstart',	0, 'int' );
        //$filter_order		= $mainframe->getUserStateFromRequest( JBCOMNAME .".japermission.filter_order",		'filter_order',		'id',	'cmd' );
        $filter_order = JRequest::getVar('filter_order', 'u.FeedID');
		$filter_order_Dir	= $mainframe->getUserStateFromRequest( JBCOMNAME .".jaxmlexport.filter_order_Dir",	'filter_order_Dir',	'DESC',			'word' );
        $search				= $mainframe->getUserStateFromRequest( JBCOMNAME .".jaxmlexport.search",			'search',			'',				'string' );
		$search				= JString::strtolower( $search );
		
		$where = null;

		if($search){
			$where[] = " (u.FeedName LIKE ". $db->Quote( '%'.$db->getEscaped( $search, true ).'%', false )." or u.description LIKE ". $db->Quote( '%'.$db->getEscaped( $search, true ).'%', false ).")";			
		}
		
		if($SchemaID)
			$where[] = " (u.SchemaID = '{$SchemaID}') ";	
		
        // just in case filter_order get's messed up
       
		if ($filter_order) {
			$orderby = ' ORDER BY '.$filter_order .' '. $filter_order_Dir;
		} else {
			$orderby = ' ORDER BY u.FeedID DESC';
		}
        
        if($where)  $where = "\n WHERE ". implode(' and ', $where);
       
        $query = "SELECT u.*, a.SchemaName as SchemaName ".
        		"\n FROM #__ja_xml_feeds u LEFT JOIN #__ja_xml_schemas a ON a.SchemaID = u.SchemaID ".
        		$where.
        		$orderby;
		$db = &JFactory::getDBO();
		$db->setQuery($query);	
		$items = $db->loadObjectList();
		
		$total = count( $items );

		jimport('joomla.html.pagination');
		$this->_pagination = new JPagination( $total, $limitstart, $limit );
		
		$list = array();
		// slice out elements based on limits
		if($items)
			$list = array_slice( $items, $this->_pagination->limitstart, $this->_pagination->limit );
		
		return $list;
    }
    
    /**
    * Return the query is used to retrive all row from database
    * @return string The query is used 
    */
    function getSchemas(){
    	global $mainframe;    	    	
    	
    	$db = & JFactory::getDBO();
    	
    	$limit		= $mainframe->getUserStateFromRequest( 'global.list.limit',		'limit',		$mainframe->getCfg('list_limit'), 'int' );
		$limitstart	= $mainframe->getUserStateFromRequest( JBCOMNAME.'.jaxmlexport.limitstart',	'limitstart',	0, 'int' );
       
        $filter_order = JRequest::getVar('filter_order', 'u.SchemaID');
		$filter_order_Dir	= $mainframe->getUserStateFromRequest( JBCOMNAME .".jaxmlexport.filter_order_Dir",	'filter_order_Dir',	'DESC',			'word' );
        $search				= $mainframe->getUserStateFromRequest( JBCOMNAME .".jaxmlexport.search",			'search',			'',				'string' );
		$search				= JString::strtolower( $search );
		
		$where = null;

		if($search){
			$where[] = " (u.SchemaName LIKE ". $db->Quote( '%'.$db->getEscaped( $search, true ).'%', false )." or u.description LIKE ". $db->Quote( '%'.$db->getEscaped( $search, true ).'%', false ).")";			
		}
				
        // just in case filter_order get's messed up
       
		if ($filter_order) {
			$orderby = ' ORDER BY '.$filter_order .' '. $filter_order_Dir;
		} else {
			$orderby = ' ORDER BY u.SchemaID DESC';
		}
		
       	
        if($where)  $where = "\n WHERE ". implode(' and ', $where);
       
        $query = "SELECT u.* ".
        		"\n FROM #__ja_xml_schemas u ".
        		$where.
        		$orderby;
		$db = &JFactory::getDBO();
		$db->setQuery($query);	
		$items = $db->loadObjectList();
		
		$total = count( $items );
		
		jimport('joomla.html.pagination');
		$this->_pagination = new JPagination( $total, $limitstart, $limit );
		
		$list = array();
		// slice out elements based on limits
		if($items)
			$list = array_slice( $items, $this->_pagination->limitstart, $this->_pagination->limit );
		
		return $list;
    }
    
    /**
	* Get page navigator object
	* @return 
	*/
	function &getPagination(){
		if ($this->_pagination == null) {
			$this->getItems();
		}
		return $this->_pagination;
	}
	
	/**
	 * Method to store a record
	 *
	 * @access	public
	 * @return	boolean	True on success
	 */
	function storeSchema()
	{
		$row =& $this->getTable("ja_xml_schemas","JobBoardTable");
		$data = JRequest::get( 'post' );

		// Bind the form fields to the hello table
		if (!$row->bind($data)) {
			$this->setError($this->_db->getErrorMsg());
			return false;
		}
		
		// Make sure the hello record is valid
		if (!$row->check()) {
			$this->setError($this->_db->getErrorMsg());
			return false;
		}

		// Store the web link table to the database
		if (!$row->store()) {
			$this->setError( $row->getErrorMsg() );
			return false;
		}

		return true;
	}

	/**
	 * Method to delete record(s)
	 *
	 * @access	public
	 * @return	boolean	True on success
	 */
	function deleteSchema()
	{
		$cids = JRequest::getVar( 'cid', array(0), 'post', 'array' );

		$row =& $this->getTable("ja_xml_schemas","JobBoardTable");

		if (count( $cids )) {
			foreach($cids as $cid) {
				if (!$row->delete( $cid )) {
					$this->setError( $row->getErrorMsg() );
					return false;
				}
			}
		}
		return true;
	}
	
	/**
	 * Method to store a record
	 *
	 * @access	public
	 * @return	boolean	True on success
	 */
	function storeFeed()
	{	
		$db =& JFactory::getDBO();
		// save the search form
		
		$MetaData = array();
		foreach ($_REQUEST as $key=>$val) {
			
			if (strpos($key, 'field_id_') !== false) {
				
				$p = explode('_', $key);
				$ElementID = array_pop($p);
				
				$MetaData[$ElementID] = $val;
				
				for ($i=1; $i < 4; $i++) {
					if (isset($_REQUEST['mf_'.$i.'_extra_'.$ElementID])) {
						$MetaData['mf_'.$i.'_extra_'.$ElementID] = $_REQUEST['mf_'.$i.'_extra_'.$ElementID];
					}
				}
				
				$sql = "SELECT field_type FROM #__ja_form_fields WHERE id = '".$val."' ";
				$db->setQuery($sql);
				$result_ff = $db->query();
				
				if ($row_ff = @mysql_fetch_array($result_ff)) {
					if($row_ff['field_type'] == 'SELECT' || $row_ff['field_type'] == 'MSELECT'){
						$MetaData['ft_'.$ElementID] = "CATEGORY";
					}
					else
						$MetaData['ft_'.$ElementID] = "TEXT";
				}
				
			}
			
			$p = explode('_', $key);
			$ElementID = array_pop($p);
			
			if (isset($_REQUEST['boolean_p_'.$ElementID])) {
				$MetaData['boolean_p_'.$ElementID] = $_REQUEST['boolean_p_'.$ElementID];
			}
			
			if (isset($_REQUEST['implode_'.$ElementID])) {
				$MetaData['implode_'.$ElementID] = $_REQUEST['implode_'.$ElementID];
			}
			
			if (isset($_REQUEST['StaticData_'.$ElementID])) {
				$MetaData['StaticData_'.$ElementID] = $_REQUEST['StaticData_'.$ElementID];
			}
		}
		//serialize for the database
		$MetaData = addslashes(serialize($MetaData));
	
		$TotalRecords = (int) JRequest::getVar('TotalRecords',"0");
	
		// build the query
		$FeedID = JRequest::getVar("FeedID", "0");
		
		$category = JRequest::getVar('search_category', '0', 'post', 'ARRAY');
		$category = implode(",", $category);
		
		if ($FeedID == "0") {
			$sql = "INSERT INTO #__ja_xml_feeds ( `FeedName` , `description` , `MetaData` , `TotalRecords` , `SchemaID`, `search_location`, `search_category`, `search_type`, `search_title`, `search_description`) VALUES ('".$this->fcnGetSQL($_REQUEST['FeedName'])."', '".$this->fcnGetSQL(JRequest::getVar('description'))."', '".$this->fcnGetSQL($MetaData)."', '".$this->fcnGetSQL(JRequest::getVar('TotalRecords'))."', '".$this->fcnGetSQL(JRequest::getVar('SchemaID'))."', '".$this->fcnGetSQL(JRequest::getVar('search_location'))."', '".$this->fcnGetSQL($category)."', '".$this->fcnGetSQL(JRequest::getVar('search_type'))."', '".$this->fcnGetSQL(JRequest::getVar('search_title'))."', '".$this->fcnGetSQL(JRequest::getVar('search_description'))."') ";
		} else {
			$sql = "UPDATE #__ja_xml_feeds SET `FeedName`='".$this->fcnGetSQL(JRequest::getVar('FeedName'))."' , `description`='".$this->fcnGetSQL(JRequest::getVar('description'))."' , `MetaData`='".$this->fcnGetSQL($MetaData)."' , `TotalRecords`='".$this->fcnGetSQL(JRequest::getVar('TotalRecords'))."', `SchemaID` = '".$this->fcnGetSQL(JRequest::getVar('SchemaID'))."', `search_location` = '".$this->fcnGetSQL(JRequest::getVar('search_location'))."', `search_category`='".$this->fcnGetSQL($category)."', `search_type`='".$this->fcnGetSQL(JRequest::getVar('search_type'))."', `search_title`='".$this->fcnGetSQL(JRequest::getVar('search_title'))."', `search_description`='".$this->fcnGetSQL(JRequest::getVar('search_description'))."'  WHERE FeedID='".$this->fcnGetSQL($FeedID)."' ";
		}
		$db =& JFactory::getDBO();
		$db->setQuery($sql);		
		$db->query();
		
		if($FeedID == "0"){
			$db->setQuery("SELECT LAST_INSERT_ID()");
			$FeedID = $db->loadResult();
		}
		
		$this->fcnExportElementChild(JRequest::getVar('SchemaID'));
		
		fcnGenerateXMLFile($FeedID);
		
		return true;
	}
	
	
	function fcnGetSQL($string){
		return mysql_real_escape_string(stripslashes($string));
	}
	
	function fcnExportElementChild($SchemaID=false) {
		$db =& JFactory::getDBO();
		
		if ($SchemaID) {
			$SchemaID_sql = " AND t1.SchemaID='".$this->fcnGetSQL($SchemaID)."' ";
		}
		
		$sql = " SELECT t1.ElementID AS EL_ID
				FROM #__ja_xml_schemas_details AS t1, #__ja_xml_schemas_details AS t2
				WHERE t1.ElementID = t2.ParentID $SchemaID_sql
				GROUP BY EL_ID ";
		$db->setQuery($sql);
		
		$result = $db->loadAssocList();
		foreach ($result as $k=>$row ) {
			$sql = "UPDATE #__ja_xml_schemas_details SET IsParent='Y' WHERE ElementID='".$this->fcnGetSQL($row['EL_ID'])."' ";
			$db->setQuery($sql);
			$db->query();
		}
		// now set the remaining NULL to 'N'
		$sql = "UPDATE #__ja_xml_schemas_details SET IsParent='N' WHERE IsParent IS NULL ";
		$db->setQuery($sql);
		$db->query();
	}
	
	/**
	 * Method to delete record(s)
	 *
	 * @access	public
	 * @return	boolean	True on success
	 */
	function deleteItems()
	{
		$cids = JRequest::getVar( 'cid', array(0), 'post', 'array' );

		$row =& $this->getTable("ja_xml_feeds","JobBoardTable");

		if (count( $cids )) {
			foreach($cids as $cid) {
				if (!$row->delete( $cid )) {
					$this->setError( $row->getErrorMsg() );
					return false;
				}
			}
		}
		return true;
	}
	
	/**
	 * Get record row
	 * 
	 * */
	/**
	 * Method to get a hello
	 * @return object with data
	 */
	function getSchemaItem()
	{
		$this->_id = JRequest::getVar('cid',  0, '', 'array');
		$this->_id = (int) $this->_id[0];
		// Load the data
		if ($this->_id != 0) {
			$query = ' SELECT * FROM #__ja_xml_schemas'.
					'  WHERE SchemaID = '.$this->_id;
			$this->_db->setQuery( $query );
			$this->_data = $this->_db->loadObject();			
		}
		else {
			$this->_data = new stdClass();
			$this->_data->SchemaID = 0;
			$this->_data->SchemaName = null;
			$this->_data->Description = null;
		}
		return $this->_data;
	}
	
	/**
	 * Method to save schema
	 * @return object with data
	 */
	function saveSchemaDetails($data)
	{
		if($data['ElementID'] == "0"){
			// Insert new element
			$sql = "INSERT INTO #__ja_xml_schemas_details (ElementName, IsCDATA, ParentID, SchemaID, FieldAttributes, StaticData, IsPivot, IsBoolean, CodeProType, CatProType, `NumTruncate`, `IsStripTag`, IsMandatory, StaticMod, NumField) VALUES ('".$this->fcnGetSQL($data['ElementName'])."', '".$this->fcnGetSQL($data['IsCDATA'])."', '".$this->fcnGetSQL($data['ParentID'])."', '".$this->fcnGetSQL($data['SchemaID'])."', '".$this->fcnGetSQL($data['FieldAttributes'])."', '".$this->fcnGetSQL($data['StaticData'])."', '".$this->fcnGetSQL($data['IsPivot'])."', '".$this->fcnGetSQL($data['IsBoolean'])."', '".$this->fcnGetSQL($data['CodeProType'])."', '".$this->fcnGetSQL($data['CatProType'])."', '".$this->fcnGetSQL($data['NumTruncate'])."', '".$this->fcnGetSQL($data['IsStripTag'])."', '".$this->fcnGetSQL($data['IsMandatory'])."', '".$this->fcnGetSQL($data['StaticMod'])."', '".$this->fcnGetSQL($data['NumField'])."' ) ";
		}else{
			$sql = "UPDATE #__ja_xml_schemas_details SET ElementName='".$this->fcnGetSQL($data['ElementName'])."', IsCDATA='".$this->fcnGetSQL($data['IsCDATA'])."', ParentID='".$this->fcnGetSQL($data['ParentID'])."', SchemaID='".$this->fcnGetSQL($data['SchemaID'])."', FieldAttributes='".$this->fcnGetSQL($data['FieldAttributes'])."', StaticData='".$this->fcnGetSQL($data['StaticData'])."', IsPivot='".$this->fcnGetSQL($data['IsPivot'])."', IsBoolean='".$this->fcnGetSQL($data['IsBoolean'])."', CodeProType='".$this->fcnGetSQL($data['CodeProType'])."', `CatProType`='".$this->fcnGetSQL($data['CatProType'])."', `NumTruncate`='".$this->fcnGetSQL($data['NumTruncate'])."', `IsStripTag`='".$this->fcnGetSQL($data['IsStripTag'])."', `IsMandatory`='".$this->fcnGetSQL($data['IsMandatory'])."', `StaticMod`='".$this->fcnGetSQL($data['StaticMod'])."', `NumField`='".$this->fcnGetSQL($data['NumField'])."' WHERE ElementID='".$this->fcnGetSQL($data['ElementID'])."' ";
		}
		// die($sql);
		$this->_db->setQuery($sql);
		
		if($this->_db->query()){
			if($data['ElementID'] == "0"){
				$this->_db->setQuery("SELECT LAST_INSERT_ID()");
				return $this->_db->loadResult();
			}else {
				return $data['ElementID'];
			}
		}else 
			return 0;
	}
	
	/**
	 * Method to get a hello
	 * @return object with data
	 */
	function getSchemaDetails()
	{
		$id = JRequest::getVar('ElementID',  0);
		// Load the data
		if ( $id != 0 ) {
			$query = ' SELECT * FROM #__ja_xml_schemas_details'.
					'  WHERE ElementID = '.$id;
			$this->_db->setQuery( $query );
			$this->_data = $this->_db->loadObject();			
		}
		else  {
			$this->_data = new stdClass();
			$this->_data->ElementID = 0;
			$this->_data->ElementName = null;
			$this->_data->IsCDATA = null;
			$this->_data->ParentID = null;
			$this->_data->FormID = null;
			$this->_data->field_id = null;
			$this->_data->SchemaID = null;
			$this->_data->FieldAttributes = null;
			$this->_data->StaticData = null;
			$this->_data->IsPivot = null;
			$this->_data->description = null;
			$this->_data->IsBoolean = null;
			$this->_data->CodeProType = null;
			$this->_data->CatProType = null;
			$this->_data->NumTruncate = null;
			$this->_data->IsStripTag = null;
			$this->_data->IsMandatory = null;
			$this->_data->StaticMod = null;
			$this->_data->NumField = null;
			$this->_data->IsParent = null;
			$this->_data->FieldComment = null;
		}
		return $this->_data;
	}
	
	/**
	 * Method to delete schema detail item
	 * @return object with data
	 */
	function deleteSchemaDetails(){
		$id = JRequest::getVar('ElementID',  0);
		// Load the data
		if ( $id != 0 ) {
			$query = ' SELECT * FROM #__ja_xml_schemas_details'.
					'  WHERE ParentID = '.$id;
			$this->_db->setQuery( $query );
			$result = $this->_db->loadAssocList();
			if(count($result) > 0){
				return false;
			}else {
				$this->_db->setQuery("DELETE FROM #__ja_xml_schemas_details WHERE ElementID = ".$id);
				$this->_db->query();
				return true;
			}
		}
		return false;
	}
	
	/**
	 * Method to get a hello
	 * @return object with data
	 */
	function getFeedItem()
	{
		$this->_id = JRequest::getVar('cid',  0, '', 'array');
		$this->_id = (int) $this->_id[0];
		// Load the data
		if (empty( $this->_data )) {
			$query = ' SELECT * FROM #__ja_xml_feeds'.
					'  WHERE FeedID = '.$this->_id;
			$this->_db->setQuery( $query );
			$this->_data = $this->_db->loadObject();
		}
		if (!$this->_data) {
			$this->_data = new stdClass();
			$this->_data->FeedID = 0;
			$this->_data->FeedName = null;
			$this->_data->description = null;
			$this->_data->MetaData = null;
			$this->_data->search_settings = null;
			$this->_data->TotalRecords = null;
			$this->_data->publish_mode = null;
			$this->_data->SchemaID = null;
			$this->_data->feed_key = null;
			$this->_data->hosts_allow = null;
			$this->_data->is_locked = null;
			$this->_data->FormID = null;
			$this->_data->include_emp_accounts = null;
			$this->_data->export_with_url = null;
			$this->_data->search_location = null;
			$this->_data->search_category = null;
			$this->_data->search_type = null;
			$this->_data->search_title = null;
			$this->_data->search_description = null;
		}
		return $this->_data;
	}
	
	function orderItem($item, $movement){
		$row = &$this->_getTable();
	    $row->load($item);
		if(!$row->move($movement)){			
			$this->setError($row->getError());
			return false;
		}
		return true;
	}	
	
	/**
    * Get field table instance
    * @return JTable Configuration table object
    */
    function &_getTable(){
        if($this->_table == null){
        	$this->_table = &JTable::getInstance('ja_feeds_categories', 'JobBoardTable');
        	
        }
		return $this->_table;
	}
		
	function setOrder($items){
		$str_user_id = implode(',',$items);	
		$total		= count( $items );
		$row		=& $this->_getTable();
		$db = JFactory::getDBO();
		$query = "SELECT id FROM #__ja_feeds_categories WHERE user_id IN ($str_user_id)";
		
		$db->setQuery($query);
		$em_id = $db->loadResultArray();
		
		$order		= JRequest::getVar( 'ordering', array(), 'post', 'array' );
		JArrayHelper::toInteger($order);

		// update ordering values
		for( $i=0; $i < $total; $i++ ) {
			$row->load( $items[$i] );
//			var_dump($order[$i]);exit;
			// track parents
			if ($row->ordering != $order[$i]) {
				$row->ordering = $order[$i];
				if (!$row->store()) {
					$this->setError($row->getError());
					return false;
				}
			} // if
		} // for
		
		return true;
	}
	
	// published categories
	function dopublish( $publish =1, $field='published')
	{
		$db		=& JFactory::getDBO();
		
		$ids = JRequest::getVar('cid', array());
		JArrayHelper::toInteger($cid, array(0));
		$ids = implode( ',', $ids );
		
		if(JRequest::getVar("tab") == "categories")
			$query = "UPDATE #__ja_feeds_categories"
			. "\n SET $field = " . intval( $publish )
			. "\n WHERE id IN ( $ids )"
			;
		else
			$query = "UPDATE #__ja_feeds_items"
			. "\n SET $field = " . intval( $publish )
			. "\n WHERE id IN ( $ids )"
			;
		$db->setQuery( $query );
		if (!$db->query()) {
			return false;
		}
		return true;
	}
	
}